/**
 * 
 */
package com.ideeli.webextractor.dao;


import static com.ideeli.webextractor.utils.Constants.IDEELI_STORE;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.ideeli.hwz.domain.HWZProduct;
import com.ideeli.webextractor.context.StaticContext;
import com.ideeli.webextractor.domain.Category;
import com.ideeli.webextractor.domain.Eb;
import com.ideeli.webextractor.domain.Manufacturer;
import com.ideeli.webextractor.domain.Product;
import com.ideeli.webextractor.utils.Utility;

/**
 * @author spaida
 * 
 */
public class ProductDaoImpl implements ProductDao
{
	private static final Logger logger = Logger.getLogger(ProductDaoImpl.class);

	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource dataSource)
	{
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public synchronized void insertProduct(Product product) throws Exception
	{
		logger.warn("insertProduct() called..");
		
		int newMaxProId = jdbcTemplate.queryForInt(
		        "select max(product_id) from `product`")+1;
		
		logger.warn("new max Product id: "+newMaxProId);
		product.setProduct_id(newMaxProId);
		
		
		String insertProdSql = "INSERT INTO `product` (`product_id`, `model`, `sku`, `upc`, `location`, `quantity`, `stock_status_id`," +
				" `image`, `manufacturer_id`, `shipping`, `price`, `points`, `tax_class_id`, `date_available`, `weight`, `weight_class_id`," +
				" `length`, `width`, `height`, `length_class_id`, `subtract`, `minimum`, `sort_order`, `status`, `date_added`," +
				" `date_modified`, `viewed`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		logger.info("executing insertProdSql..\n ["+insertProdSql+"]");
		jdbcTemplate.update(insertProdSql,newMaxProId,
				product.getModel(),
				product.getSku(),
				product.getUpc(),
				product.getLocation(),
				product.getQuantity(),
				product.getStock_status_id(),
				product.getImage(),
				product.getManufacturer_id(),
				product.getShipping(),
				product.getPrice(),
				product.getPoints(),
				product.getTax_class_id(),
				product.getDate_available(),
				product.getWeight(),
				product.getWeight_class_id(),
				product.getLength(),
				product.getWidth(),
				product.getHeight(),
				product.getLength_class_id(),
				product.getSubtract(),
				product.getMinimum(),
				product.getSort_order(),
				product.getStatus(),
				product.getDate_added(),
				product.getDate_modified(),
				product.getViewed());
		
		
		
		String insertProdDescSql = "INSERT INTO `product_description` (`product_id`, `language_id`, `name`, `description`, " +
				"`meta_description`, `meta_keyword`) VALUES (?,?,?,?,?,?)";
		logger.warn("insertProdDescSql..\n ["+insertProdDescSql+"]");
		jdbcTemplate.update(insertProdDescSql,newMaxProId,
				product.getLanguage_id(),
				product.getName(),
				product.getDescription(),
				product.getMeta_description(),
				product.getMeta_keyword());
		
		
		String insertProdStore="INSERT INTO `product_to_store` (`product_id`, `store_id`) VALUES (?,?)";
		logger.warn("insertProdStore.. ["+insertProdStore+"]");
		jdbcTemplate.update(insertProdStore,newMaxProId,IDEELI_STORE);
		
		String insertProdSupplier="INSERT INTO `product_to_supplier` (`product_id`, `supplier_id`) VALUES (?,?)";
		logger.warn("insertProdSupplier.. ["+insertProdSupplier+"]");
		jdbcTemplate.update(insertProdSupplier,newMaxProId,product.getSupplierId());

		
		int newMaxImageId = jdbcTemplate.queryForInt(
		        "select max(product_image_id) from `product_image`")+1;
		
		String insertProdImage="INSERT INTO `product_image` (`product_image_id`, `product_id`, `image`, `sort_order`) " +
				"VALUES (?,?,?,?)";
		logger.warn("insertProdImage..\n ["+insertProdImage+"]");
		jdbcTemplate.update(insertProdImage,newMaxImageId,newMaxProId,product.getLargeImage(),0);
		
		String insertProdPrice="INSERT INTO `product_price` (`product_id`, `base_price`, `profit_percent`,`tax`,`S_plusminus`) " +
				"VALUES (?,?,?,?,?)";
		logger.warn("insertProdPrice..\n ["+insertProdPrice+"]");
		jdbcTemplate.update(insertProdPrice,newMaxProId,product.getBase_price(),product.getProfit_percent(),product.getTax(), product.getPlusMinus() );

	}

	/*@Override
	public void findAllProducts() throws Exception
	{
		logger.warn("findAllProducts() called..");
		List<Product> productDetailList = jdbcTemplate.query("select * from product", new ProductMapper());

	}*/
	
	@Override
	public Map<String,Integer> loadAllSkuVpn(String tableName) throws Exception
	{
		logger.warn("loadAllSkuVpn() called..");
		String sql="select p.product_id as id, sku, upc, model, supplier_id  from "+tableName;

/*		if("product".equals(tableName))
		{
			sql="select product_id as id, sku, upc  from "+tableName;
		}
		else if (tableName.contains("parse_history"))
		{
			sql="select p.product_id as id, sku, upc  from "+tableName;
		}
*/		
		Map<String,Integer> skuVpnIdMap=null; 
		logger.info("executing loadAllSkuVpn..\n ["+sql+"]");
		try
		{
			skuVpnIdMap= (Map<String,Integer>)jdbcTemplate.queryForObject(sql, new SkuVpnMapper());	
		}
		catch(EmptyResultDataAccessException empty)
		{
			logger.info("empty result set - loadAllSkuVpn-"+tableName);
			skuVpnIdMap= new HashMap<String,Integer>(); 
		}
		

		return skuVpnIdMap;
	}
	
	
	public void markInactiveOutofStock(List<Integer> prods) throws Exception
	{
		logger.warn("markInactiveOutofStock"+prods);
		//stock_status_id=5
		//quantity=0
		//status=0
		//delete from product_to_store, product_to_category,
		String whereClause=getInClause(prods);
		String sql="update product set stock_status_id=5, quantity=0, status=0 where product_id in "+whereClause;
		jdbcTemplate.execute(sql);
		sql="delete from product_to_store where product_id  in "+whereClause;
		jdbcTemplate.execute(sql);
		sql="delete from product_to_category where product_id  in "+whereClause;
		jdbcTemplate.execute(sql);

		
	}
	
	public void updateFeaturedProdList(String prodclause) throws Exception
	{
		String sql="update setting "
				+ "set value ='"+prodclause+"' "
				+ "where setting_id='28882'";
		logger.info(sql);
		jdbcTemplate.execute(sql);
	}
	
	private static final class SkuVpnMapper implements RowMapper
	{
		@Override
		public Map<String, Integer> mapRow(ResultSet rs, int arg1) throws SQLException {
			
			Map<String, Integer> skuUpc= new HashMap<String, Integer>();
			
			do
			{
				int sid=rs.getInt("supplier_id");
				String skuupc=rs.getString("sku");
				skuupc+=rs.getString("upc");
				
				if(sid==2)
					skuupc+=rs.getString("model");
				
				int id=rs.getInt("id");
				skuUpc.put(skuupc, id);
			}
			while(rs.next());
			
			return skuUpc;
		}
	}

	private static final class ProductMapper implements RowMapper<Product>
	{

		public Product mapRow(ResultSet rs, int rowNum) throws SQLException
		{
			Product product = new Product();
			product.setProduct_id(rs.getInt("product_id"));
			product.setBase_price(rs.getFloat("base_price"));
			product.setSku(rs.getString("sku"));
			product.setTax(rs.getFloat("tax"));
			product.setProfit_percent(rs.getFloat("profit_percent"));

			return product;
		}
	}
	
	private static final class ProductIdNameMapper implements RowMapper
	{
		@Override
		public Map<Integer, String> mapRow(ResultSet rs, int arg1) throws SQLException {
			
			Map<Integer, String> productIdName= new HashMap<Integer, String>();
			
			do
			{
				Integer productId=rs.getInt("product_id");
				String name=rs.getString("name");
				productIdName.put(productId, name);
			}
			while(rs.next());
			
			return productIdName;
		}
	}

	@Override
	public synchronized void insertCategory(Category category) throws Exception {
		
		logger.warn("insertCategory..");
		int maxCatId = jdbcTemplate.queryForInt(
		        "select max(category_id) from `category`")+1;
		
		logger.info("max Product id: "+maxCatId);
		category.setCategory_id(maxCatId);
		
		String insertCategorySql="INSERT INTO `category` (`category_id`, `image`, `parent_id`, `top`, `column`," +
				" `sort_order`, `status`, `date_added`, `date_modified`) VALUES ('"+maxCatId+"', '" +
				category.getImage()+"', '" +
				category.getParent_id()+"', '" +
				category.getTop()+"', '" +
				category.getColumn()+"', '" +
				category.getSort_order()+"', '" +
				category.getStatus()+"', '" +
				category.getDate_added()+"', '" +
				category.getDate_modified()+"')";
		
		logger.info("executing insertCategorySql..\n ["+insertCategorySql+"]");
		jdbcTemplate.update(insertCategorySql);

				
		String insertCategoryDescriptionSql="INSERT INTO `category_description` (`category_id`, `language_id`, `name`, `description`," +
				" `meta_description`, `meta_keyword`) VALUES ('"+maxCatId+"', '" +
				category.getLanguage_id()+"', '" +
				category.getName()+"', '" +
				category.getDescription()+"', '" +
				category.getMeta_description()+"', '" +
				category.getMeta_keyword()+"')";
		
		logger.info("executing insertCategoryDescriptionSql..\n ["+insertCategoryDescriptionSql+"]");
		jdbcTemplate.update(insertCategoryDescriptionSql);
		
		String insertCategoryStore="INSERT INTO `category_to_store` (`category_id`, `store_id`) VALUES (?,?)";
		
		logger.info("executing insertProdStore..\n ["+insertCategoryStore+"]");
		jdbcTemplate.update(insertCategoryStore,maxCatId,IDEELI_STORE);
		
		
	}

	@Override
	public int findCategoryIdByName(String name) throws Exception
	{
		
		logger.warn("findCategoryIdByName.. catName: "+name);
		int categoryId = -1;
		try
		{
			categoryId= this.jdbcTemplate.queryForInt(
			        "select `category_id` from `category_description` where `name` = ?", name);
		}
		catch(EmptyResultDataAccessException e)
		{
			logger.info("Category name not found return -1..");
		}
		  
		return categoryId; 
	}
	
	public List<Product> loadProuctsPrice (String sql) throws Exception
	{
		logger.warn("findProductsToCategory() called..");
		
		List<Product> productList = jdbcTemplate.query(sql, new ProductMapper());
		
		return productList;
	}
	
	
	
	
	@Override
	public synchronized void insertProdToCat(int product_id, int category_id) throws Exception
	{
		logger.warn("insertProdToCat()");
		String insertCategorySql="INSERT INTO `product_to_category` " +
				"(`product_id`, `category_id`) VALUES (?, ?)";
		
		logger.info("executing insertProdSql..\n ["+insertCategorySql+"]");
		jdbcTemplate.update(insertCategorySql, product_id, category_id);
		
//		INSERT INTO `product_to_category` (`product_id`, `category_id`) VALUES ('55', '57');
//		INSERT INTO `product_to_store` (`product_id`, `store_id`) VALUES ('55', '0');
	}
	
	@Override
	public synchronized int insertSupplier(String name) throws Exception {
		
		int suppId = jdbcTemplate.queryForInt("select max(supplier_id) from `product_supplier`")+1;
		logger.warn("max Supplier id: "+suppId);
		
		String insertSupplierSql="INSERT INTO `product_supplier` (`supplier_id`," +
		       " `name`) VALUES (?,?)";
		
		logger.warn("insertSupplierSql ["+insertSupplierSql+"]");
		jdbcTemplate.update(insertSupplierSql,suppId,name);
		
		return suppId;

	}


	@Override
	public synchronized void insertManufacturer(Manufacturer manufacturer) throws Exception {
		logger.warn("insertManufacturer() called..");
		
		int maxManuId = jdbcTemplate.queryForInt(
		        "select max(manufacturer_id) from `manufacturer`")+1;
		
		logger.warn("max Manufacturer id: "+maxManuId);
		manufacturer.setManufacturer_id(maxManuId);
		
		String insertManufacturerSql="INSERT INTO `manufacturer` (`manufacturer_id`," +
				" `name`, `image`, `sort_order`) VALUES (?,?,?,?)";

		logger.warn("insertManufacturer ["+insertManufacturerSql+"]");
		jdbcTemplate.update(insertManufacturerSql, maxManuId , manufacturer.getName()
				,manufacturer.getImage(),manufacturer.getSort_order());
		
		
		String insertManufacturerStore="INSERT INTO `manufacturer_to_store` (`manufacturer_id`, `store_id`) VALUES (?,?)";
		
		logger.warn("insertManufacturerStore.. ["+insertManufacturerStore+"]");
		jdbcTemplate.update(insertManufacturerStore,manufacturer.getManufacturer_id(),IDEELI_STORE);

		int maxBannerImageId = jdbcTemplate.queryForInt("select max(banner_image_id) from `banner_image`")+1;
		logger.warn("max banner id: "+maxBannerImageId);
		manufacturer.setBannerId(maxBannerImageId);
		
		String insertBannerImage="INSERT INTO `banner_image` (`banner_image_id`,`banner_id`,`link`, `image`) VALUES (?,?,?,?)";
		
		String link=null;
		StaticContext staticCache=StaticContext.getStaticContext();
		
		if(staticCache.isHomeRun())
			link="/ideelicart/index.php?route=product/manufacturer/product&manufacturer_id="+maxManuId;
		else
			link="/index.php?route=product/manufacturer/product&manufacturer_id="+maxManuId;
		
		jdbcTemplate.update(insertBannerImage, maxBannerImageId,8,link,manufacturer.getBannerImage());
		
		String insertBannerImageDesc="INSERT INTO `banner_image_description` (`banner_image_id`,`banner_id`,`language_id`, `title`) VALUES (?,?,?,?)";
		jdbcTemplate.update(insertBannerImageDesc, maxBannerImageId,8,1,manufacturer.getTitle());
	}
	
	@Override
	public int findManufacturerByName(String name) throws Exception
	{
		logger.warn("findCategoryIdByName..");
		int manufacturerId = -1;
		try
		{
			manufacturerId= this.jdbcTemplate.queryForInt(
			        "select `manufacturer_id` from `manufacturer` where `name` = ?", name);
		}
		catch(EmptyResultDataAccessException e)
		{
			logger.info("manufacturer name not found return -1..");
		}
		  
		return manufacturerId; 
		
	}
	
	@Override
	public int findSupplierByName(String name) throws Exception
	{
		logger.warn("findSupplierByName..");
		int supplierId = -1;
		try
		{
			supplierId= this.jdbcTemplate.queryForInt(
			        "select `supplier_id` from `product_supplier` where `name` = ?", name);
		}
		catch(EmptyResultDataAccessException e)
		{
			logger.info("supplier name not found return -1..");
		}
		  
		return supplierId; 
		
	}
	
	@Override
	public void updatePrice(int pId, float showPrice, float percent,float base_price) throws Exception
	{
		logger.debug("updatePrice:"+pId);
		
		String sql="update product_price set profit_percent=?, base_price=? where product_id=?";
		logger.debug("sql.. ["+sql+"]");
		jdbcTemplate.update(sql,percent,base_price, pId);
		
		sql="update product set price=? where product_id=?";
		logger.debug("sql.. ["+sql+"]");
		jdbcTemplate.update(sql,showPrice, pId);
		
	}

/*
	@Override
	public Map<String, Object> loadCategories() throws Exception {
		
		logger.warn("findAllProducts() called..");
		Map<String, Object> catNameId = jdbcTemplate.queryForMap("select * from product", new CategoryCacheMapper());
		
		return catNameId;
	}
	
	private static final class CategoryCacheMapper implements RowMapper<Map<String, Object>>
	{
		private Map<String, Integer> catNameId= new HashMap<String, Integer>();

		public Map<String, Integer> mapRow(ResultSet rs, int rowNum) throws SQLException
		{
			
			return null;
		}
	}
*/
	@Override
	public synchronized void insertParsedProduct(String sku, String vpn,String model, String desc, int prodId) throws Exception {
		
		logger.debug("insertParseCategory() called..");
		
		int newMaxProId = jdbcTemplate.queryForInt(
		        "select max(id) from `parse_history`")+1;
		
		
		String insertParseCategory="INSERT INTO `parse_history` (`id`, `sku`, `upc`,`model`,`product_id`, `description`, `ts`) VALUES (?,?,?,?,?,?,?)";

		logger.debug("executing insertParseCategory Sql..\n ["+insertParseCategory+"]");
		jdbcTemplate.update(insertParseCategory,newMaxProId,sku, vpn,model, prodId, desc, Utility.getCurrentTS());
		
	}
	
	public List<String> getImageNamesDB() throws Exception
	{
		logger.warn("getImageNamesDB() called..");
		String sql="select image from product_image";
		List<String> imagNames=jdbcTemplate.queryForList(sql, String.class); 
		
		return imagNames;
	}
	
	@Override
	public List<Product> findAllOrderedProducts() throws Exception
	{
		logger.warn("findProductsOrdered() called..");
		String sql="select p.product_id as product_id, pp.base_price as base_price, pp.profit_percent as profit_percent, p.sku as sku, pp.tax as tax, ps.supplier_id as sid" 
			+" from product p, order_product op, product_price pp, product_to_supplier ps"
			+" where p.product_id=op.product_id"
			+" and   p.product_id=pp.product_id"
			+" and   p.product_id=ps.product_id";;
		
		List<Product> products= jdbcTemplate.query(sql, new ProductMapper());

		return products;
	}
	
	
	public void deleteProduct(List<Integer> prodIds) throws Exception {
		
		List<String> sqls=getProdDelSqls(prodIds);
		
		for(String sql: sqls)
		{
			logger.warn("executing  ["+sql+"]");
			jdbcTemplate.update(sql);
		}
		
	}

	private String getInClause(List<Integer> prodIds)
	{
		StringBuilder stBuilder= new StringBuilder("(");
		
		if(prodIds.size()<1)
		{
			logger.info("prodIDs empty no products to delte.");
			return "(-1)";
		}
			
		
		for(int i: prodIds)
		{
			stBuilder.append(i).append(",");
		}
		
		stBuilder.deleteCharAt(stBuilder.length()-1);
		stBuilder.append(")");
		
		
		return stBuilder.toString();
	}
	
	
	public void correctBannerImagePath() throws Exception
	{
		
	}
	
	
	

	@Override
	public void trimNameChangeAll() throws Exception
	{
		String sql="select product_id, name  from product_description";
		Map<Integer,String> idNameMap= (Map<Integer,String>)jdbcTemplate.queryForObject(sql, new ProductIdNameMapper());
		
		for(Integer productId: idNameMap.keySet())
		{
			String name=idNameMap.get(productId);
			name=Utility.getProductName(name);
			sql="update `product_description` set name='"+name+"' WHERE product_id='"+productId+"'";
			logger.warn("["+sql+"]");
			jdbcTemplate.execute(sql);
		}
		
	}
	
	@Override	
	public Map<String, List<String>> getCategoryProdsWithImages(int maxP)
	{
		String sql="SELECT CONCAT( cat.category_id, ':', ptoc.product_id ) " +
				"FROM category cat, product_to_category ptoc, product p, product_description pd " +
				"WHERE ptoc.product_id = p.product_id " +
				"AND ptoc.category_id = cat.category_id " +
				"AND p.product_id = pd.product_id " +
				"AND p.image LIKE 'data/images/large%' " +
				"AND p.price >100 " +
				"AND p.price < "+maxP+ " "+ 
				"order by cat.category_id, ptoc.product_id  desc";
		
		logger.warn("["+sql+"]");
		
		Map<String,List<String>> catToProds= new HashMap<String,List<String>>();
		List<String> catsProds=jdbcTemplate.queryForList(sql, String.class);
		for(String catProd: catsProds)
		{
			String cp[]=catProd.split(":");
			String cat=cp[0];
			String prod=cp[1];
			
			if(catToProds.containsKey(cat))
			{
				List<String> prods=catToProds.get(cat);
				prods.add(prod);
			}
			else
			{
				List<String> prods= new ArrayList<String>();
				prods.add(prod);
				catToProds.put(cat,prods);
				
			}
		}
		
		return catToProds;
		
	}
	
	@Override	
	public Map<String, List<String>> getCategoryProdsForFeaturedList()
	{
		String sql="SELECT CONCAT( cat.category_id, ':', ptoc.product_id ) " +
				"FROM category cat, product_to_category ptoc, product p, product_description pd " +
				"WHERE ptoc.product_id = p.product_id " +
				"AND ptoc.category_id = cat.category_id " +
				"AND p.product_id = pd.product_id " +
				"AND length(pd.name) > 10 " +
				"AND p.image LIKE 'data/images/large%' " +
				//"AND cat.category_id in (440,400,222,419,445,402,403,468,439,415,251) " +
				"AND p.price > 120 " +
				"AND p.price < 2000 "; 
				//+ "order by cat.category_id, ptoc.product_id  desc";
		
		logger.warn("["+sql+"]");
		
		Map<String,List<String>> catToProds= new HashMap<String,List<String>>();
		List<String> catsProds=jdbcTemplate.queryForList(sql, String.class);
		for(String catProd: catsProds)
		{
			String cp[]=catProd.split(":");
			String cat=cp[0];
			String prod=cp[1];
			
			if(catToProds.containsKey(cat))
			{
				List<String> prods=catToProds.get(cat);
				prods.add(prod);
			}
			else
			{
				List<String> prods= new ArrayList<String>();
				prods.add(prod);
				catToProds.put(cat,prods);
				
			}
		}
		
		return catToProds;
		
	}
	
	public List<HWZProduct> getHWZProducts(String inClause) throws Exception
	{
		String sql="SELECT p.product_id AS pid, pd.name AS title, pd.description AS description, " +
				"p.sku AS image, p.price AS price, cat.name as cat " +
				"FROM product p, product_description pd, category_description cat, product_to_category ptoc " +
				"WHERE p.product_id = pd.product_id " +
				"AND ptoc.product_id = p.product_id " +
				"AND ptoc.category_id = cat.category_id " +
				"AND p.product_id IN " + inClause;
		
		logger.warn("["+sql+"]");
		
		List<HWZProduct> products=jdbcTemplate.query(sql, new HWZProductMapper());
		
		return  products;
		
	}
	
	private static final class HWZProductMapper implements RowMapper<HWZProduct>
	{

		public HWZProduct mapRow(ResultSet rs, int rowNum) throws SQLException
		{
			HWZProduct product = new HWZProduct();
			product.setProductId(rs.getString("pid"));
			product.setTitle(rs.getString("title"));
			
			java.sql.Blob blob = rs.getBlob("description");
			byte[] bdata = blob.getBytes(1, (int) blob.length());
			String text = new String(bdata);
			product.setDescription(text);
			product.setImage(rs.getString("image"));
			product.setPrice(rs.getFloat("price"));
			product.setCategory(rs.getString("cat"));
			
			return product;
		}
	}
	
	@Override
	public List<Product> getAllProducts()
	{
		String sql="select p.product_id,`model`,`sku`,`upc`,`location`,`quantity`,`stock_status_id`,pi.image,`manufacturer_id`,`shipping`,`price`,`points`,`tax_class_id`,`date_available`,`weight`,`weight_class_id`,`length`,`width`,`height`,`length_class_id`,`subtract`,`minimum`,p.sort_order,`status`,`date_added`,`date_modified`,`viewed`,`language_id`,`name`,`description`,`meta_description`,`meta_keyword`,`supplier_id`" +
		" from product p, product_description pd, product_to_supplier ps, product_image pi " +
		" where p.product_id=pd.product_id and p.product_id=ps.product_id and p.product_id=pi.product_id ";
		

		logger.warn("["+sql+"]");

		List<Product> products=jdbcTemplate.query(sql,new ProductSuperMapper());

		return products;

	}
	

	
	@Override
	public List<Product> getEbayProducts(String prodIds) 
	{
		String sql="select p.product_id,`model`,`sku`,`upc`,`location`,`quantity`,`stock_status_id`,pi.image,`manufacturer_id`,`shipping`,`price`,`points`,`tax_class_id`,`date_available`,`weight`,`weight_class_id`,`length`,`width`,`height`,`length_class_id`,`subtract`,`minimum`,p.sort_order,`status`,`date_added`,`date_modified`,`viewed`,`language_id`,`name`,`description`,`meta_description`,`meta_keyword`,`supplier_id`" +
				" from product p, product_description pd, product_to_supplier ps, product_image pi " +
				" where p.product_id=pd.product_id and p.product_id=ps.product_id and p.product_id=pi.product_id and" +
				" p.product_id in "+prodIds;
		
		logger.warn("["+sql+"]");
		
		List<Product> products=jdbcTemplate.query(sql,new ProductSuperMapper());
		
		return products;
	}
	
	private static final class ProductSuperMapper implements RowMapper<Product>
	{

		public Product mapRow(ResultSet rs, int rowNum) throws SQLException
		{
			Product product = new Product();
			
			
			product.setProduct_id(rs.getInt("p.product_id"));
			product.setModel(rs.getString("p.model"));
			product.setSku(rs.getString("p.sku"));
			product.setUpc(rs.getString("p.upc"));
			product.setLocation(rs.getString("p.location"));
			product.setQuantity(rs.getInt("p.quantity"));
			product.setStock_status_id(rs.getInt("p.stock_status_id"));
			product.setManufacturer_id(rs.getInt("p.manufacturer_id"));
			product.setShipping(rs.getShort("p.shipping"));
			product.setPrice(rs.getFloat("p.price"));
			product.setPoints(rs.getInt("p.points"));
			product.setTax_class_id(rs.getInt("p.tax_class_id"));
			product.setDate_available(rs.getDate("p.date_available"));
			product.setWeight(rs.getFloat("p.weight"));
			product.setWeight_class_id(rs.getInt("p.weight_class_id"));
			product.setLength(rs.getFloat("p.length"));
			product.setWidth(rs.getFloat("p.width"));
			product.setHeight(rs.getFloat("p.height"));
			product.setLength_class_id(rs.getInt("p.length_class_id"));
			product.setSubtract(rs.getShort("p.subtract"));
			product.setMinimum(rs.getInt("p.minimum"));
			product.setSort_order(rs.getInt("p.sort_order"));
			product.setStatus(rs.getShort("p.status"));
			product.setDate_added(rs.getDate("p.date_added"));
			product.setDate_modified(rs.getDate("p.date_modified"));
			product.setViewed(rs.getInt("p.viewed"));
			product.setLanguage_id(rs.getInt("pd.language_id"));
			product.setName(rs.getString("pd.name"));
			product.setDescription(rs.getString("pd.description"));
			product.setMeta_description(rs.getString("pd.meta_description"));
			product.setMeta_keyword(rs.getString("pd.meta_keyword"));
			product.setSupplierId(rs.getInt("ps.supplier_id"));
			product.setImage(rs.getString("pi.image"));
			
			return product;
		}
	}
	
	@Override
	public List<Eb> getEbEligEntries()
	{
		
		String sql="SELECT p.product_id as pid, p.price as price, cat.category_id as catId, cat.name as catName "+
					"FROM category_description cat, product_to_category ptoc, product p, product_description pd "+
					"WHERE ptoc.product_id = p.product_id "+ 
					"AND ptoc.category_id = cat.category_id "+ 
					"AND p.product_id = pd.product_id  "+
					"AND p.image LIKE 'data/images/large%' "+  
					"AND p.price > 50  "+
					"AND p.price < 1001  "+
					"order by cat.category_id, ptoc.product_id  desc";
		
//		sql="SELECT p.product_id as pid, p.price as price, cat.category_id as catId, cat.name as catName FROM category_description cat, product_to_category ptoc, product p, product_description pd WHERE ptoc.product_id = p.product_id AND ptoc.category_id = cat.category_id AND p.product_id = pd.product_id  AND p.product_id=4398";
		
		logger.warn("["+sql+"]");
		
		List<Eb> ebProds=jdbcTemplate.query(sql,new RowMapper<Eb> (){

			@Override
			public Eb mapRow(ResultSet rs, int ind) throws SQLException
			{				
				Eb eb = new Eb();
				eb.setProdId(rs.getInt("pid"));
				eb.setCatId(rs.getInt("catId"));
				eb.setPrice(rs.getFloat("price"));
				eb.setCatName(rs.getString("catName"));
				
				return eb;
			}
			
		}
		);
		
		return ebProds;
		
	}
	
	public List<Long> getActiveProdIds()
	{
		String sql="select product_id from product where stock_status_id=7";
		return jdbcTemplate.queryForList(sql,Long.class);
	}
	

	
	private List<String> getProdDelSqls(List<Integer> prodIds)
	{
		List<String> delSqls=new ArrayList<String>();
		
		String whrClause=getInClause(prodIds);
		
		delSqls.add("delete from product where product_id in "+whrClause);
		delSqls.add("delete from product_description where product_id in "+whrClause);
		delSqls.add("delete from product_to_category where product_id not in (select product_id from product)");
		delSqls.add("delete from product_to_store where product_id not in (select product_id from product)");
		delSqls.add("delete from product_image where product_id not in (select product_id from product)");
		delSqls.add("delete from product_to_supplier where product_id not in (select product_id from product)");
		delSqls.add("delete from product_price where product_id not in (select product_id from product)");
		
		delSqls.add("delete from category where category_id not in (select category_id from product_to_category) and parent_id <> 0");
		delSqls.add("delete from category_description where category_id not in (select category_id from category )");
		delSqls.add("delete from category_to_store where category_id not in (select category_id from category)");
		delSqls.add("delete from parse_history where  ts < current_date - 2");
		
//		delSqls.add("delete from manufacturer where manufacturer_id not in (select manufacturer_id from product)");
//		delSqls.add("delete from manufacturer_to_store where manufacturer_id not in (select manufacturer_id from product)");
		
		
		return delSqls;
	}

	

}
